﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Xml.Linq;
using LightWeightORM.ORM;
using LightWeightORM.ORM.DbHelper;
using LightWeightORM.ORM.MappingAttribute;
using LightWeightORM.ORM.Utility;


namespace LightWeightORM
{
    namespace ORM
    {
        namespace TableAccessor
        {

            /// <summary>
            /// Summary description for TableAccessor
            /// </summary>
            public class TableAccessor<TEntity>
            {
                private IDbHelper DbHelper { get; set; }
                public string TableName { get; set; }

                public TableAccessor(string connectionString)
                {
                    this.TableName = EntityUtility.GetTableName(typeof(TEntity));
                    this.DbHelper = new SQLiteHelper(connectionString);
                }
                public DataTable ToDataTable()
                {
                    string commandText = string.Format("SELECT * FROM {0}", this.TableName); 

                    DataTable dtResult = new DataTable();
                    DbHelper.Fill(dtResult, commandText);
                    return dtResult;
                }
                public DataTable ToDataTable(params Filter[] filters)
                {
                    return this.DataTableWithFilter(filters);
                }

                private DataTable DataTableWithFilter(params Filter[] filters)
                {
                    StringBuilder sbSelect = new StringBuilder();
                    sbSelect.AppendFormat("  SELECT * FROM {0} WHERE", this.TableName);

                    List<DbParameter> parms = new List<DbParameter>();
                    for (int i = 0; i < filters.Length; i++)
                    {
                        if (i == filters.Length - 1) //最后一个Filter
                            sbSelect.AppendFormat("  {0}", filters[i].ConditionExpression());
                        else
                            sbSelect.AppendFormat("  {0} AND", filters[i].ConditionExpression());
                        parms.Add(filters[i].ToParameter());
                    }

                    DataTable dtResult = new DataTable();
                    this.DbHelper.Fill(dtResult, sbSelect.ToString(), parms.ToArray());

                    return dtResult;
                }
                private DataRowCollection DataRows(params Filter[] filters)
                {
                    return this.DataTableWithFilter(filters).Rows;
                }
                private DataRow FirstRow(params Filter[] filters)
                {
                    DataTable dt = this.DataTableWithFilter(filters);
                    if (dt.Rows.Count <= 0)
                        return null;
                    else
                        return dt.Rows[0];
                }
                public int RecordCount(params Filter[] filters)
                {
                    StringBuilder sbSelect = new StringBuilder();
                    sbSelect.AppendFormat("  SELECT Count(*) FROM {0} WHERE", this.TableName);

                    List<DbParameter> parms = new List<DbParameter>();
                    for (int i = 0; i < filters.Length; i++)
                    {
                        if (i == filters.Length - 1) //最后一个Filter
                            sbSelect.AppendFormat("  {0}", filters[i].ConditionExpression());
                        else
                            sbSelect.AppendFormat("  {0} AND", filters[i].ConditionExpression());

                        DbParameter parm = filters[i].ToParameter();
                        parms.Add(parm);
                    }
                    return Convert.ToInt32(this.DbHelper.ExecuteScalar(sbSelect.ToString(), parms.ToArray()));
                }

                /// <summary>
                /// Get an Entity that fit with filter
                /// </summary>
                /// <param name="filter"></param>
                /// <returns>an Entity, null if there no Entity machted</returns>
                public TEntity GetOne(params Filter[] filters)
                {
                    TEntity entity = (TEntity)Activator.CreateInstance(typeof(TEntity));

                    List<string> fields = EntityUtility.GetFields(typeof(TEntity));
                    DataRow row = this.FirstRow(filters);

                    if (row == null)
                        return default(TEntity);   //return default value of TEntity, that is null

                    foreach (string field in fields)
                    { 
                        //Set field's value through reflection
                        EntityUtility.SetFieldValue(entity, field, row[field]);
                    }

                    return entity;
                }

                public TEntity[] GetMuch(params Filter[] filters)
                {
                    List<TEntity> entitys = new List<TEntity>();
                    List<string> fields = EntityUtility.GetFields(typeof(TEntity));

                    DataTable dtResult = this.DataTableWithFilter(filters);
                    foreach (DataRow row in dtResult.Rows)
                    {
                        TEntity entity = (TEntity)Activator.CreateInstance(typeof(TEntity));  
                        foreach (string field in fields)
                        {
                            //Set field's value through reflection
                            EntityUtility.SetFieldValue(entity, field, row[field]);
                        }
                        entitys.Add(entity);
                    }

                    return entitys.ToArray();
                }
                //TODO:Implement code repeat with GetMuch, so refactoring later
                public TEntity[] GetAll()
                {
                    List<TEntity> entitys = new List<TEntity>();
                    List<string> fields = EntityUtility.GetFields(typeof(TEntity));

                    DataTable dtResult = this.ToDataTable();
                    foreach (DataRow row in dtResult.Rows)
                    {
                        TEntity entity = (TEntity)Activator.CreateInstance(typeof(TEntity));
                        foreach (string field in fields)
                        {
                            //Set field's value through reflection
                            EntityUtility.SetFieldValue(entity, field, row[field]);
                        }
                        entitys.Add(entity);
                    }

                    return entitys.ToArray();
                }

                public int Insert(TEntity entity)
                {
                    Type tEntity = entity.GetType();
                    //Get the Table Name through reflection
                    string tableName = EntityUtility.GetTableName(tEntity);

                    //Get the Table Fields through reflection
                    List<string> fields = EntityUtility.GetFields(tEntity);


                    //Build SQL statement
                    StringBuilder sbInsert = new StringBuilder();
                    sbInsert.AppendFormat("  INSERT INTO {0}", tableName);
                    sbInsert.AppendFormat("  ({0})", EntityUtility.ParseSqlField(fields));
                    sbInsert.AppendFormat("  VALUES({0})", EntityUtility.ParseSqlParameter(fields));

                    //Add parameters
                    List<DbParameter> parms = new List<DbParameter>();
                    PropertyInfo[] prosInfo = tEntity.GetProperties();
                    foreach (PropertyInfo proInfo in prosInfo)
                    {
                        string fieldName = string.Empty;

                        object[] attDataField = null;
                        attDataField = proInfo.GetCustomAttributes(typeof(DataFieldAttribute), false);
                        if (attDataField != null)
                        {
                            fieldName = ((DataFieldAttribute)attDataField[0]).FieldName;
                        }
                        object value = proInfo.GetValue(entity, null);

                        DbParameter parm = DbHelper.CreateParameter();
                        parm.ParameterName = DbHelper.ParameterModifier + fieldName;
                        parm.Value = value;
                        parms.Add(parm);
                    }

                    return this.DbHelper.ExecuteNonQuery(sbInsert.ToString(),parms.ToArray());
                }
                public int Delete(TEntity entity)
                {
                    Type tEntity = entity.GetType();
                    //Get the primary key through reflection
                    string primaryKey = EntityUtility.GetPrimaryKey(tEntity);
                    if (primaryKey == string.Empty)
                    {
                        throw new Exception(string.Format("Primary key not found, Entity:{0}", tEntity.FullName));
                    }

                    //Build SQL statement
                    StringBuilder sbDelete = new StringBuilder();
                    sbDelete.AppendFormat("  DELETE FROM {0}", EntityUtility.GetTableName(tEntity));
                    sbDelete.AppendFormat("  WHERE {0}={1}{2}", primaryKey, DbHelper.ParameterModifier, primaryKey);

                    //Add parameters
                    DbParameter parm = DbHelper.CreateParameter();
                    parm.ParameterName = DbHelper.ParameterModifier + primaryKey;
                    parm.Value = EntityUtility.GetPrimaryKeyValue(entity);

                    return DbHelper.ExecuteNonQuery(sbDelete.ToString(), parm);
                }
                public int Update(TEntity entity)
                {
                    Type tEntity = entity.GetType();
                    //Get the Table Name through reflection
                    string tableName = EntityUtility.GetTableName(tEntity);
                    string primaryKey = EntityUtility.GetPrimaryKey(tEntity);
                    if (primaryKey == string.Empty)
                    {
                        throw new Exception(string.Format("Primary key not found, Entity:{0}", tEntity.FullName));
                    }

                    //Get the Table Fields through reflection
                    List<string> fields = EntityUtility.GetFields(tEntity);


                    //Build SQL statement
                    StringBuilder sbUpdate = new StringBuilder();
                    sbUpdate.AppendFormat("  UPDATE {0}", tableName);
                    sbUpdate.AppendFormat("  SET {0}", EntityUtility.ParseSqlFieldWithParamter(fields));
                    sbUpdate.AppendFormat("  WHERE {0}={1}{2}", primaryKey,DbHelper.ParameterModifier, primaryKey);

                    //Add parameters
                    List<DbParameter> parms = new List<DbParameter>();
                    foreach (string fieldName in fields)
                    {
                        DbParameter parm = DbHelper.CreateParameter();
                        parm.ParameterName = DbHelper.ParameterModifier + fieldName;
                        parm.Value = EntityUtility.GetFieldValue(entity, fieldName);
                        parms.Add(parm);
                    }

                    return DbHelper.ExecuteNonQuery(sbUpdate.ToString(),parms.ToArray());
                }
            }
        }
    }
}